/**
 * Abbreviations:	ph = phone	fb = facebook	cmt = comment	sbcb = subscribe	
 * 'TOPIC' table's data structure:
 * 		Use CREATOR_NUM + TITLE to identify a unique topic. 
 * 		a newly created topic = creator_num + title + content + photo, other fields is empty
 * 		a subscribed topic = creator_num + title + subscriber_num,  other fields is empty
 * 		a commented topic = creator_num + title + subscriber_num + text_cmt + photo_cmt 
 * 	Use different search conditions to distinguish these objects.
 */
package com.server;

import java.io.ByteArrayInputStream;
import java.io.DataInputStream;
import java.io.DataOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.ServerSocket;
import java.net.Socket;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


class Server {
	static final String GET_CONNECTION = "jdbc:derby:db;create=true";
	static final int PORT = 9999;

	public static void main(String[] args) throws Exception {
		Connection 	con = DriverManager.getConnection(GET_CONNECTION);
		Statement st = con.createStatement();
		DatabaseMetaData dmd = con.getMetaData();
		
	    ResultSet rsUser = dmd.getTables(null, null, "USERS", null);
		if (rsUser.next())
			st.executeUpdate("update USERS set IS_ONLINE=0");
		else
			st.executeUpdate("create table USERS (PHONE_NUM VARCHAR(32), PASSWORD VARCHAR(32), NAME VARCHAR(32), AVATAR BLOB, FACEBOOK_ID VARCHAR(32), IS_ONLINE SMALLINT, PRIMARY KEY (PHONE_NUM))");
		ResultSet rsTopic = dmd.getTables(null, null, "TOPIC", null);
		if (!rsTopic.next()) 
			st.executeUpdate("create table TOPIC (CREATOR_NUM VARCHAR(32), TITLE VARCHAR(32), CONTENT VARCHAR(32), PHOTO BLOB, SUBSCRIBER_NUM VARCHAR(32), TEXT_CMT VARCHAR(32), PHOTO_CMT BLOB)");
		
		st.close();
		con.close();
		
		ServerSocket serverSocket = new ServerSocket(PORT);
		while (true) {
			( new Session(serverSocket.accept()) ).start();
		}
	}
}



class Session extends Thread {
	// constant
	final int REGISTER_INTERACT = 101;
	final int CHECK_ACCOUNT_INTERACT = 102;

	final int RECEIVE_MY_FB_ID = 201;
	final int RECEIVE_CREATED_TOPIC = 202;
	final int RECEIVE_SUBSCRIBE_TOPIC = 203;
	final int RECEIVE_UNSUBSCRIBE_TOPIC = 204;
	final int RECEIVE_ADD_COMMENT = 205;
	
	final int PROVIDE_ONLINE_FRIEND = 301;
	final int PROVIDE_USER_CREATED_TOPIC = 302;
	final int PROVIDE_COMMENT_AND_SUBSCRIBE = 303;
	final int PROVIDE_MY_SUBSCRIBED_TOPIC = 304;
	final int PROVIDE_MY_COMMENTED_TOPIC = 305;

	// field
	Socket socket;
	DataOutputStream dos;
	DataInputStream dis;
	Connection con;

	
	public Session(Socket socket) throws IOException, SQLException {
		this.socket = socket;
		dos = new DataOutputStream(socket.getOutputStream());
		dis = new DataInputStream(socket.getInputStream());
		con = DriverManager.getConnection(Server.GET_CONNECTION);
	}

	
	public void run() {
		try {
			switch (dis.readInt()) {
			case REGISTER_INTERACT:
				registerInteract();
				break;
			case CHECK_ACCOUNT_INTERACT:
				checkAccountInteract();
				break;

			case RECEIVE_MY_FB_ID:
				receiveMyFbId();
				break;
			case RECEIVE_CREATED_TOPIC:
				receiveCreatedTopic();
				break;
			case RECEIVE_SUBSCRIBE_TOPIC:
				receiveSubscribeTopic();
				break;
			case RECEIVE_UNSUBSCRIBE_TOPIC:
				receiveUnsubscribeTopic();
				break;
			case RECEIVE_ADD_COMMENT:
				receiveAddComment();
				break;

			case PROVIDE_ONLINE_FRIEND:
				provideOnlineFriend();
				break;
			case PROVIDE_USER_CREATED_TOPIC:
				provideUserCreatedTopic();
				break;
			case PROVIDE_COMMENT_AND_SUBSCRIBE:
				provideCommentAndSubscribe();
				break;
			case PROVIDE_MY_SUBSCRIBED_TOPIC:
				provideMySubscribedTopic();
				break;
			case PROVIDE_MY_COMMENTED_TOPIC:
				provideMyCommentedTopic();
				break;
			default:
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dis.close();
				dos.close();
				socket.close();
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	
	public boolean isExistAccount(String phNum) throws SQLException {
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select * from USERS where PHONE_NUM='" + phNum + "'");
		boolean result = rs.next();
		st.close();
		return result;
	}

	
	void registerInteract() throws IOException, InterruptedException, SQLException {
		String phNum = dis.readUTF();
		if(isExistAccount(phNum))
			dos.writeBoolean(false);
		else {
			dos.writeBoolean(true);
			try {
				PreparedStatement pst = con.prepareStatement("insert into USERS values(?,?,?,?,?,?)");
				pst.setString(1, phNum);
				pst.setString(2, dis.readUTF()); // PASSWORD
				pst.setString(3, dis.readUTF()); // NAME
				InputStream is = new ByteArrayInputStream(Tool.receiveByteArray(dis));
				pst.setBlob(4, is); // AVATAR
				pst.setString(5, null);	// FACEBOOK_ID
				pst.setBoolean(6, true); // IS_ONLINE
				pst.execute();
				is.close();
				pst.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	
	void checkAccountInteract() throws IOException, SQLException, InterruptedException {
		String phNum = dis.readUTF();
		if (isExistAccount(phNum)) {
			dos.writeBoolean(true);
			String password = dis.readUTF();

			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("select * from USERS where PHONE_NUM='" + phNum + "' and PASSWORD='" + password + "'");
			if (rs.next()) {
				dos.writeBoolean(true);
				// send self name and avatar
				dos.writeUTF(rs.getString(3));
				Tool.sendByteArray(dos, rs.getBytes(4));
				// indicate this account is online
				st.executeUpdate("update USERS set IS_ONLINE=1 where PHONE_NUM='" + phNum + "'");
			} else
				dos.writeBoolean(false);
			st.close();
		} else
			dos.writeBoolean(false);
	}


	void receiveMyFbId() throws IOException, SQLException {
		String myPhNum = dis.readUTF();
		String myFbId = dis.readUTF();
	
		Statement st = con.createStatement();
		st.executeUpdate("update USERS set FACEBOOK_ID='" + myFbId + "' where PHONE_NUM='" + myPhNum + "'");
		st.close();
	}

	
	void receiveCreatedTopic() throws IOException, InterruptedException {
		try {
			// TODO handle insert repeate value
			PreparedStatement pst = con.prepareStatement("insert into TOPIC (CREATOR_NUM, TITLE, CONTENT, PHOTO) values(?,?,?,?)");
			pst.setString(1, dis.readUTF());
			pst.setString(2, dis.readUTF());
			pst.setString(3, dis.readUTF());
			InputStream is = new ByteArrayInputStream(Tool.receiveByteArray(dis));
			pst.setBlob(4, is);
			pst.execute();
			is.close();
			pst.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	
	void receiveSubscribeTopic() throws IOException, SQLException {
		PreparedStatement pst = con.prepareStatement("insert into TOPIC (CREATOR_NUM, TITLE, SUBSCRIBER_NUM) values(?,?,?)");
		pst.setString(1, dis.readUTF());
		pst.setString(2, dis.readUTF());
		pst.setString(3, dis.readUTF());
		pst.execute();
		pst.close();
	}

	
	void receiveUnsubscribeTopic() throws IOException, SQLException {
		String creatorNum = dis.readUTF();
		String title = dis.readUTF();
		String subscriberNum = dis.readUTF();

		Statement st = con.createStatement();
		st.executeUpdate("delete from TOPIC where CREATOR_NUM='" + creatorNum + "' and TITLE='" + title + "' and SUBSCRIBER_NUM='" + subscriberNum + "' and TEXT_CMT is null");
		st.close();
	}

	
	void receiveAddComment() throws IOException, InterruptedException, SQLException {
		try {
			PreparedStatement pst = con.prepareStatement("insert into TOPIC (CREATOR_NUM, TITLE, SUBSCRIBER_NUM, TEXT_CMT, PHOTO_CMT) values(?,?,?,?,?)");
			pst.setString(1, dis.readUTF());
			pst.setString(2, dis.readUTF());
			pst.setString(3, dis.readUTF());
			pst.setString(4, dis.readUTF());
			InputStream is = new ByteArrayInputStream(Tool.receiveByteArray(dis));
			pst.setBlob(5, is); 
			pst.execute();
			is.close();
			pst.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	
	void provideOnlineFriend() throws IOException, InterruptedException, SQLException {
		int contactsFrdCount = dis.readInt();
		for (int i = 0; i < contactsFrdCount; i++) {
			String contactsFrdPhNum = dis.readUTF();

			Statement stContactsFrd = con.createStatement();
			ResultSet rsContactsFrd = stContactsFrd.executeQuery("select NAME, AVATAR from USERS where PHONE_NUM='" + contactsFrdPhNum + "' and IS_ONLINE=1");
			if (rsContactsFrd.next()) {
				dos.writeBoolean(true);
				dos.writeUTF(contactsFrdPhNum);
				dos.writeUTF(rsContactsFrd.getString("NAME")); 
				Tool.sendByteArray(dos, rsContactsFrd.getBytes("AVATAR")); 
			} else
				dos.writeBoolean(false);
			stContactsFrd.close();
		}
		
		if(dis.readBoolean()) {
			int fbFrdCount = dis.readInt();
			for (int i = 0; i < fbFrdCount; i++) {
				String fbId = dis.readUTF();
				
				Statement stFbFrd = con.createStatement();
				ResultSet rsFbFrd = stFbFrd.executeQuery("select PHONE_NUM, NAME, AVATAR from USERS where FACEBOOK_ID='" + fbId + "' and IS_ONLINE=1");
				if (rsFbFrd.next()) {
					dos.writeBoolean(true);
					dos.writeUTF(rsFbFrd.getString("PHONE_NUM"));
					dos.writeUTF(rsFbFrd.getString("NAME")); 
					Tool.sendByteArray(dos, rsFbFrd.getBytes("AVATAR")); 
				} else
					dos.writeBoolean(false);
				stFbFrd.close();
			}
		}
	}

	
	void provideUserCreatedTopic() throws IOException, InterruptedException, SQLException {
		String creatorNum = dis.readUTF();

		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select TITLE, CONTENT, PHOTO from TOPIC where CREATOR_NUM='" + creatorNum + "' and TITLE is not null and CONTENT is not null and PHOTO is not null");

		ArrayList<Topic> topicList = new ArrayList<Topic>();
		while (rs.next()) {
			Topic topic = new Topic();
			topic.setTitle(rs.getString("TITLE"));
			topic.setContent(rs.getString("CONTENT"));
			topic.setPhoto(rs.getBytes("PHOTO"));
			topicList.add(topic);
		}
		st.close();

		if (topicList.isEmpty())
			dos.writeInt(0);
		else {
			dos.writeInt(topicList.size());
			for (Topic topic : topicList) {
				dos.writeUTF(topic.getTitle());
				dos.writeUTF(topic.getContent());
				Tool.sendByteArray(dos, topic.getPhoto());
			}
		}
	}

	
	void provideCommentAndSubscribe() throws IOException, InterruptedException, SQLException {
		String creatorNum = dis.readUTF();
		String title = dis.readUTF();

		Statement stCmt = con.createStatement();
		ResultSet rsCmt = stCmt.executeQuery("select SUBSCRIBER_NUM, TEXT_CMT, PHOTO_CMT from TOPIC where CREATOR_NUM='" + creatorNum + "' and TITLE='" + title + "' and SUBSCRIBER_NUM is not null and TEXT_CMT is not null");

		ArrayList<Comment> cmtList = new ArrayList<Comment>();
		while (rsCmt.next()) {
			String textCmt = rsCmt.getString("TEXT_CMT");
			byte[] photoCmt = rsCmt.getBytes("PHOTO_CMT");
			String sbcbNum = rsCmt.getString("SUBSCRIBER_NUM");
			
			Statement tempSt = con.createStatement();
			ResultSet tempRs = tempSt.executeQuery("select NAME, AVATAR from USERS where PHONE_NUM='" + sbcbNum + "'");
			tempRs.next();
			String name = tempRs.getString("NAME");
			byte[] avatar = tempRs.getBytes("AVATAR");
			tempSt.close();
			
			cmtList.add(new Comment(avatar, name, sbcbNum, textCmt, photoCmt));
		}
		stCmt.close();

		Statement stSbcb = con.createStatement();
		ResultSet rsSbcb = stSbcb.executeQuery("select SUBSCRIBER_NUM from TOPIC where CREATOR_NUM='" + creatorNum + "' and TITLE='" + title + "' and SUBSCRIBER_NUM is not null and TEXT_CMT is null");
		
		ArrayList<Subscriber> sbcbList = new ArrayList<Subscriber>();
		while (rsSbcb.next()) {
			String phNum = rsSbcb.getString("SUBSCRIBER_NUM");
			
			Statement tempSt = con.createStatement();
			ResultSet tempRs = tempSt.executeQuery("select NAME, AVATAR from USERS where PHONE_NUM='" + phNum + "'");
			tempRs.next();
			String name = tempRs.getString("NAME");
			byte[] avatar = tempRs.getBytes("AVATAR");
			tempSt.close();

			sbcbList.add(new Subscriber(avatar, name, phNum));
		}
		stSbcb.close();
		
		// send
		if (cmtList.isEmpty())
			dos.writeInt(0);
		else {
			dos.writeInt(cmtList.size());
			for(Comment cmt : cmtList) {
				Tool.sendByteArray(dos, cmt.getAvatar());
				dos.writeUTF(cmt.getName());
				dos.writeUTF(cmt.getPhNum());
				dos.writeUTF(cmt.getTextCmt());
				Tool.sendByteArray(dos, cmt.getPhotoCmt());
			}
		}

		if (sbcbList.isEmpty())
			dos.writeInt(0);
		else {
			dos.writeInt(sbcbList.size());
			for(Subscriber sbcb : sbcbList) {
				Tool.sendByteArray(dos, sbcb.getAvatar());
				dos.writeUTF(sbcb.getName());
				dos.writeUTF(sbcb.getPhNum());
			}
		}
		
		// check if user subscribed this topic
		String userPhoneNum = dis.readUTF();
		Statement stIsJoined = con.createStatement();
		ResultSet rsIsJoined = stIsJoined.executeQuery("select * from TOPIC where CREATOR_NUM='" + creatorNum + "' and TITLE='" + title + "' and SUBSCRIBER_NUM='" + userPhoneNum + "' and TEXT_CMT is null");
		boolean isJoined = rsIsJoined.next();
		stIsJoined.close();
		dos.writeBoolean(isJoined);
	}
	
	
	void provideMySubscribedTopic() throws IOException, SQLException, InterruptedException {
		String sbcbNum = dis.readUTF();

		Statement stTemp = con.createStatement();
		ResultSet rsTemp = stTemp.executeQuery("select CREATOR_NUM, TITLE from TOPIC where SUBSCRIBER_NUM='" + sbcbNum + "' and TITLE is not null and CONTENT is null and TEXT_CMT is null");

		ArrayList<Topic> topicList = new ArrayList<Topic>();
		while (rsTemp.next()) {
			String creatorNum = rsTemp.getString("CREATOR_NUM");
			String title = rsTemp.getString("TITLE");

			Topic topic = new Topic();
			topic.setCreatorPhNum(creatorNum);
			topic.setTitle(title);

			Statement stBody = con.createStatement();
			ResultSet rsBody = stBody.executeQuery("select CONTENT, PHOTO from TOPIC where CREATOR_NUM='" + creatorNum + "' and TITLE='" + title + "' and CONTENT is not null and PHOTO is not null");
			rsBody.next();
			topic.setContent(rsBody.getString("CONTENT"));
			topic.setPhoto(rsBody.getBytes("PHOTO"));
			
			topicList.add(topic);
			stBody.close();
		}
		stTemp.close();

		if (topicList.isEmpty())
			dos.writeInt(0);
		else {
			dos.writeInt(topicList.size());
			for (Topic topic : topicList) {
				dos.writeUTF(topic.getCreatorPhNum());
				dos.writeUTF(topic.getTitle());
				dos.writeUTF(topic.getContent());
				Tool.sendByteArray(dos, topic.getPhoto());
			}
		}
	}
	
	
	void provideMyCommentedTopic() throws IOException, SQLException, InterruptedException {
		String sbcbNum = dis.readUTF();

		Statement stTemp = con.createStatement();
		ResultSet rsTemp = stTemp.executeQuery("select CREATOR_NUM, TITLE from TOPIC where SUBSCRIBER_NUM='" + sbcbNum + "' and TEXT_CMT is not null");

		ArrayList<Topic> topicList = new ArrayList<Topic>();
		while (rsTemp.next()) {
			String creatorNum = rsTemp.getString("CREATOR_NUM");
			String title = rsTemp.getString("TITLE");

			Topic topic = new Topic();
			topic.setCreatorPhNum(creatorNum);
			topic.setTitle(title);

			Statement stBody = con.createStatement();
			ResultSet rsBody = stBody.executeQuery("select CONTENT, PHOTO from TOPIC where CREATOR_NUM='" + creatorNum + "' and TITLE='" + title + "' and CONTENT is not null and PHOTO is not null");
			rsBody.next();
			topic.setContent(rsBody.getString("CONTENT"));
			topic.setPhoto(rsBody.getBytes("PHOTO"));
			
			topicList.add(topic);
			stBody.close();
		}
		stTemp.close();

		if (topicList.isEmpty())
			dos.writeInt(0);
		else {
			dos.writeInt(topicList.size());
			for (Topic topic : topicList) {
				dos.writeUTF(topic.getCreatorPhNum());
				dos.writeUTF(topic.getTitle());
				dos.writeUTF(topic.getContent());
				Tool.sendByteArray(dos, topic.getPhoto());
			}
		}
	}
}



class Tool {
	static final int MILLISECOND = 150;
	static final int NUMBER = 512;
	
	
	public static void sendByteArray(DataOutputStream dos, byte[] byteArray) throws IOException, InterruptedException {
		dos.writeInt(byteArray.length);
		
		int currentPosition = 0;
		int byteNumberToCopy = 0;
		while (currentPosition != byteArray.length) {
			byteNumberToCopy = (byteArray.length-currentPosition >= NUMBER) ? NUMBER : byteArray.length-currentPosition;
			byte[] tempByteArray = new byte[byteNumberToCopy];
			System.arraycopy(byteArray, currentPosition, tempByteArray, 0, byteNumberToCopy);
			currentPosition += byteNumberToCopy;
			dos.write(tempByteArray, 0, byteNumberToCopy);
		}
		Thread.sleep(MILLISECOND);
	}

	
	public static byte[] receiveByteArray(DataInputStream dis) throws IOException, InterruptedException {
		byte[] byteArray = new byte[dis.readInt()];
		
		int currentPosition = 0;
		int byteNumberToCopy = 0;
		while (currentPosition != byteArray.length) {
			byteNumberToCopy = (byteArray.length-currentPosition >= NUMBER) ? NUMBER : byteArray.length-currentPosition;
			byte[] tempByteArray = new byte[byteNumberToCopy];
			dis.read(tempByteArray, 0, byteNumberToCopy);
			System.arraycopy(tempByteArray, 0, byteArray, currentPosition, byteNumberToCopy);
			currentPosition += byteNumberToCopy;
		}
		Thread.sleep(MILLISECOND);
		
		return byteArray;
	}
}



class Comment extends Subscriber{
	String textCmt;
	byte[] photoCmt;
	
	public Comment(byte[] avatar, String name, String phNum, String textCmt, byte[] photoCmt) {
		super(avatar, name, phNum);
		this.textCmt = textCmt;
		this.photoCmt = photoCmt;
	}

	public String getTextCmt() {
		return textCmt;
	}

	public byte[] getPhotoCmt() {
		return photoCmt;
	}
}



class Subscriber {
	byte[] avatar;
	String name;	
	String phNum;	// The phNum is not shown, because we want to protect user's privacy.
	
	public Subscriber(byte[] avatar, String name, String phNum) {
		super();
		this.avatar = avatar;
		this.name = name;
		this.phNum = phNum;
	}

	public byte[] getAvatar() {
		return avatar;
	}

	public String getName() {
		return name;
	}

	public String getPhNum() {
		return phNum;
	}
}



class Topic {
	String creatorPhNum;
	String title;
	String content;
	byte[] photo;

	public String getCreatorPhNum() {
		return creatorPhNum;
	}

	public void setCreatorPhNum(String creatorPhNum) {
		this.creatorPhNum = creatorPhNum;
	}

	public String getTitle() {
		return title;
	}
	
	public void setTitle(String title) {
		this.title = title;
	}
	
	public String getContent() {
		return content;
	}
	
	public void setContent(String content) {
		this.content = content;
	}
	
	public byte[] getPhoto() {
		return photo;
	}
	
	public void setPhoto(byte[] photo) {
		this.photo = photo;
	}
}
